﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using AAA.DataAccess.Meta;

namespace AAA.DataAccess
{
    public abstract class AbstractDatabaseDataAccess : AbstractDataAccess
    {
        private Dictionary<object, string> _dicSQLStatement;
        private Dictionary<object, List<CriteriaInfo>> _dicCriteria;
        private List<CriteriaInfo> _lstCriteria;
        private Dictionary<object, List<string>> _dicGeneralSQL;
        private Dictionary<object, List<string>> _dicGroupBy;
        private Dictionary<object, List<string>> _dicOrderBy;
        private Dictionary<object, List<string>> _dicSummaryField;
        private Dictionary<object, Dictionary<string, string>> _dicAliasNameMapping;
        private List<string> _lstGroupBy;
        private List<string> _lstOrderBy;
        private List<string> _lstSummaryField;
        private List<string> _lstGeneralSQL;
        public const string GROUP_BY_AS = "~AS~";

        
        public AbstractDatabaseDataAccess()
        {
            _dicSQLStatement = new Dictionary<object, string>();
            _dicCriteria = new Dictionary<object, List<CriteriaInfo>>();
            _lstCriteria = new List<CriteriaInfo>();
            _dicGeneralSQL = new Dictionary<object, List<string>>();
            _dicGroupBy = new Dictionary<object, List<string>>();
            _dicOrderBy = new Dictionary<object, List<string>>();
            _dicSummaryField = new Dictionary<object, List<string>>();
            _dicAliasNameMapping = new Dictionary<object, Dictionary<string, string>>();
            _lstGeneralSQL = new List<string>();
            _lstGroupBy = new List<string>();
            _lstOrderBy = new List<string>();
            _lstSummaryField = new List<string>();
        }

        private char _cSplitDelimeter;

        public char SplitDelimeter
        {
            get { return _cSplitDelimeter; }
            set { _cSplitDelimeter = value; }
        }

        public string AddCriteria(string strStatement, string[] strCheckFields)
        {
            return AddCriteria(null, strStatement, strCheckFields, CriteriaTypeEnum.Single, NullValueProcessEnum.SKIP);
        }

        public string AddCriteria(string strStatement, string[] strCheckFields, CriteriaTypeEnum eCriteriaType)
        {
            return AddCriteria(null, strStatement, strCheckFields, eCriteriaType, NullValueProcessEnum.SKIP);
        }

        public string AddCriteria(string strStatement, string[] strCheckFields, NullValueProcessEnum eNullValueProcess)
        {
            return AddCriteria(null, strStatement, strCheckFields, CriteriaTypeEnum.Single, eNullValueProcess);
        }

        public string AddCriteria(object oName, string strStatement, string[] strCheckFields)
        {
            return AddCriteria(oName, strStatement, strCheckFields, CriteriaTypeEnum.Single, NullValueProcessEnum.SKIP);
        }

        public string AddCriteria(object oName, string strStatement, string[] strCheckFields, CriteriaTypeEnum eCriteriaType)
        {
            return AddCriteria(oName, strStatement, strCheckFields, eCriteriaType, NullValueProcessEnum.SKIP);
        }

        public string AddCriteria(object oName, string strStatement, string[] strCheckFields, NullValueProcessEnum eNullValueProcess)
        {
            return AddCriteria(oName, strStatement, strCheckFields, CriteriaTypeEnum.Single, eNullValueProcess);
        }

        public string AddCriteria(object oName, string strStatement, string[] strCheckFields, CriteriaTypeEnum eCriteriaType, NullValueProcessEnum eNullValueProcess)
        {
            CriteriaInfo criteriaInfo = new CriteriaInfo(strStatement, strCheckFields, eCriteriaType, eNullValueProcess);

            if (SplitDelimeter != null)
                criteriaInfo.SplitDelimeter = SplitDelimeter;

            int iParamIndex = 0;
            if (oName == null)
            {
                iParamIndex = _lstCriteria.Count;
                _lstCriteria.Add(criteriaInfo);
            }
            else
            {
                if (!_dicCriteria.ContainsKey(oName))
                    _dicCriteria.Add(oName, new List<CriteriaInfo>());
                iParamIndex = _dicCriteria[oName].Count;
                _dicCriteria[oName].Add(criteriaInfo);
            }
            return "{" + iParamIndex + "}";
        }


        public void AddGroupBy(object oName, List<string> lstGroupBy)
        {
            foreach (string strGroupBy in lstGroupBy)
                AddGroupBy(oName, strGroupBy);
        }

        public void AddGroupBy(object oName, string strGroupBy)
        {
            AddGroupBy(oName, strGroupBy, null);
        }

        public void AddGroupBy(object oName, string strGroupBy, string strAlias)
        {
            if (oName == null)
            {
                _lstGroupBy.Add(strAlias == null
                                    ? strGroupBy
                                    : strGroupBy + GROUP_BY_AS + strAlias);
            }
            else
            {
                if (!_dicGroupBy.ContainsKey(oName))
                    _dicGroupBy.Add(oName, new List<string>());

                _dicGroupBy[oName].Add(strAlias == null
                                        ? strGroupBy
                                        : strGroupBy + GROUP_BY_AS + strAlias);
            }
        }

        public void AddOrderBy(object oName, List<string> lstOrderBy)
        {
            foreach (string strOrderBy in lstOrderBy)
                AddOrderBy(oName, strOrderBy);
        }

        public void AddOrderBy(object oName, string strOrderBy)
        {
            if (oName == null)
            {
                _lstOrderBy.Add(strOrderBy);
            }
            else
            {
                if (!_dicOrderBy.ContainsKey(oName))
                    _dicOrderBy.Add(oName, new List<string>());

                _dicOrderBy[oName].Add(strOrderBy);
            }
        }

        public void AddGeneralSQL(object oName, string strStatement)
        {
            if (oName == null)
            {
                _lstGeneralSQL.Add(strStatement);
            }
            else
            {
                if (!_dicGeneralSQL.ContainsKey(oName))
                    _dicGeneralSQL.Add(oName, new List<string>());

                _dicGeneralSQL[oName].Add(strStatement);
            }
        }

        public List<string> GetAllGroupBy(object oName)
        {
            if (!_dicGroupBy.ContainsKey(oName))
                return new List<string>();

            return _dicGroupBy[oName];
        }

        public void AddSummaryField(object oName, List<string> lstFunction)
        {
            foreach (string strFunction in lstFunction)
            {
                AddSummaryField(oName, strFunction);
            }
        }
        public void AddSummaryField(string strFunction, string strFieldName)
        {
            AddSummaryField(null, strFunction, strFieldName);
        }

        public void AddSummaryField(object oName, string strFunction)
        {
            AddSummaryField(oName, strFunction, "");
        }

        public void AddSummaryField(object oName, string strFunction, string strFieldName)
        {
            string strField = strFunction + " " + strFieldName;

            if (oName == null)
            {
                _lstSummaryField.Add(strField);
            }
            else
            {
                if (!_dicSummaryField.ContainsKey(oName))
                    _dicSummaryField.Add(oName, new List<string>());
                _dicSummaryField[oName].Add(strField);
            }
        }

        public List<string> GetAllSummaryField(object oName)
        {
            if (!_dicSummaryField.ContainsKey(oName))
                return new List<string>();

            return _dicSummaryField[oName];
        }

        protected void AddSQLStatement(object oName, string strSQL)
        {
            if (_dicSQLStatement.ContainsKey(oName))
                _dicSQLStatement[oName] = strSQL;
            else
                _dicSQLStatement.Add(oName, strSQL);

            if (_dicCriteria.ContainsKey(oName))
                _dicCriteria[oName] = _lstCriteria;
            else
                _dicCriteria.Add(oName, _lstCriteria);
            /*
                        if (_dicGroupBy.ContainsKey(oName))
                            _dicGroupBy[oName] = _lstGroupBy;
                        else
                            _dicGroupBy.Add(oName, _lstGroupBy);

                        if (_dicSummaryField.ContainsKey(oName))
                            _dicSummaryField[oName] = _lstSummaryField;
                        else
                            _dicSummaryField.Add(oName, _lstSummaryField);
            */
            _lstCriteria = new List<CriteriaInfo>();
            _lstGroupBy = new List<string>();
            _lstSummaryField = new List<string>();
        }

        protected List<object> SQLNameList
        {
            get
            {
                return new List<object>(_dicSQLStatement.Keys);
            }
        }

        protected string GetSQLStatement(object oName)
        {
            return _dicSQLStatement.ContainsKey(oName)
                    ? _dicSQLStatement[oName]
                    : null;
        }

        private string[] GenerateParamList(object oName, Dictionary<string, object> dicValue, List<string> lstField)
        {
            List<CriteriaInfo> lstCriteria = _dicCriteria[oName];
            string[] strParams = new string[lstCriteria.Count];

            for (int i = 0; i < lstCriteria.Count; i++)
            {
                strParams[i] = lstCriteria[i].GenerateStatement(dicValue, lstField);
            }
            return strParams;
        }

        private string ProcessGroup(object oDataName, string strSQLStatement)
        {
            string strProcessed = strSQLStatement;
            string strGroupBy = "";
            string strSummary = "";
            string strGroupByStatement = "";
            int iStartIndex;
            int iLastWhereIndex;

            if (_dicGroupBy.ContainsKey(oDataName) == false)
                return strProcessed;

            if (_dicGroupBy[oDataName].Count == 0)
                return strProcessed;

            for (int i = 0; i < _dicGroupBy[oDataName].Count; i++)
            {
                strGroupBy += ", " + _dicGroupBy[oDataName][i];
                strGroupByStatement += ", " + _dicGroupBy[oDataName][i].Substring(0, _dicGroupBy[oDataName][i].IndexOf(GROUP_BY_AS));
            }
            strGroupBy = strGroupBy.Substring(1);
            strGroupByStatement = strGroupByStatement.Substring(1);

            for (int i = 0; i < _dicSummaryField[oDataName].Count; i++)
                strSummary += "," + _dicSummaryField[oDataName][i];

            if (strSummary.Length > 0)
                strSummary = strSummary.Substring(1);

            strGroupByStatement = " group by " + strGroupByStatement;

            strProcessed = "select " + strGroupBy.Replace(GROUP_BY_AS, " ") + (strSummary.Length > 0 ? ", " + strSummary : "") +
                           strProcessed.Substring(strProcessed.ToLower().IndexOf("from"));

            iLastWhereIndex = strProcessed.ToLower().LastIndexOf("where");
            iStartIndex = strProcessed.ToLower().IndexOf("having", iLastWhereIndex + 1);

            //包含having的statement
            if ((iStartIndex > -1) && (iStartIndex > iLastWhereIndex))
            {
                strProcessed = strProcessed.Substring(0, iStartIndex) +
                               strGroupByStatement +
                               strProcessed.Substring(iStartIndex);
                return strProcessed;
            }

            iStartIndex = strProcessed.ToLower().IndexOf("order by", iLastWhereIndex + 1);

            if (iStartIndex > -1)
            {
                strProcessed = strProcessed.Substring(0, iStartIndex) +
                               strGroupByStatement + " " +
                               strProcessed.Substring(iStartIndex);
            }
            else
            {
                strProcessed += " " + strGroupByStatement + strGroupByStatement.Replace("group by", "order by");
            }



            return strProcessed;
        }

        private string ProcessGeneralSQL(object oDataName, string strSQLStatement)
        {
            if (!_dicGeneralSQL.ContainsKey(oDataName))
                return strSQLStatement;

            List<string> lstGeneralSQL = _dicGeneralSQL[oDataName];
            string strGeneralSQL = "";
            foreach (string strStatement in lstGeneralSQL)
                strGeneralSQL += " " + strStatement;
            return strGeneralSQL + " " + strSQLStatement;
        }

        protected bool ExecuteSQL(object oDataName, string strSQL)
        {
            return ExecuteSQL(oDataName, strSQL, GetAllParam());
        }

        private string ProcessOrderBy(object oDataName, string strSQLStatement)
        {
            string strProcessed = strSQLStatement;
            string strOrderBy = "";
            List<string> lstOrderBy;

            if (_dicOrderBy.ContainsKey(oDataName) == false)
                return strProcessed;

            if (_dicOrderBy[oDataName].Count == 0)
                return strProcessed;

            lstOrderBy = _dicOrderBy[oDataName];

            for (int i = 0; i < lstOrderBy.Count; i++)
                strOrderBy += "," + lstOrderBy[i];

            if (strOrderBy.Length > 0)
                strProcessed += " order by " + strOrderBy.Substring(1);

            return strProcessed;
        }

        private string ProcessNullValue(string strConvertSQL, Dictionary<string, object> dicValue, List<string> lstInsertField)
        {
            if (strConvertSQL.Trim().ToLower().StartsWith("insert") == false)
                return strConvertSQL;

            int iStartIndex = strConvertSQL.ToLower().IndexOf("value") + "value".Length;
            int iEndIndex = 0;
            string strKey = "";            
            List<string> lstNullField = new List<string>();

            while((iStartIndex = strConvertSQL.IndexOf('@', iStartIndex)) > -1)
            {
                iEndIndex = Math.Min(strConvertSQL.IndexOf(',', iStartIndex), strConvertSQL.IndexOf(')', iStartIndex));
                if (iEndIndex < 0)
                    iEndIndex = strConvertSQL.IndexOf(')', iStartIndex);

                strKey = strConvertSQL.Substring(iStartIndex + 1, iEndIndex - iStartIndex);

                if (lstInsertField.Contains(strKey.Replace(",", "").Replace(")", "").Trim()) == false)
                    lstInsertField.Add(strKey.Replace(",", "").Replace(")", "").Trim());

                if(dicValue.ContainsKey(strKey.Replace(",", "").Replace(")", "").Trim()) == false)
                    lstNullField.Add("@" + strKey);

                iStartIndex = iEndIndex;
            }

            foreach (string strNullField in lstNullField)
                strConvertSQL = strConvertSQL.Replace(strNullField, "null" + strNullField.Substring(strNullField.Length - 1));

            return strConvertSQL;
        }

        protected bool ExecuteSQL(object oDataName, string strSQL, Dictionary<string, object> dicValue)
        {
            bool isSuccess = false;
            //string strErrorMessage;

            string[] strFields;
            object[] oArgs;

            string strConvertSQL;
            List<string> lstField = new List<string>();
            List<string> lstInsertField = new List<string>();
            int iUpdateRowCount;
            int iParamIndex;

            try
            {
                DateTime dtTime1 = DateTime.Now;
                if (dicValue == null)
                    dicValue = new Dictionary<string, object>();

                strConvertSQL = string.Format(strSQL, GenerateParamList(oDataName, dicValue, lstField));

                DateTime dtTime11 = DateTime.Now;
                strFields = new string[lstField.Count];
                oArgs = new object[lstField.Count];

                for (int i = 0; i < lstField.Count; i++)
                {
                    strFields[i] = lstField[i];
                    oArgs[i] = dicValue.ContainsKey(lstField[i]) ? dicValue[lstField[i]] : null;
                }
                DateTime dtTime12 = DateTime.Now;
                strConvertSQL = ProcessGroup(oDataName, strConvertSQL);
                DateTime dtTime13 = DateTime.Now;
                strConvertSQL = ProcessGeneralSQL(oDataName, strConvertSQL);
                DateTime dtTime14 = DateTime.Now;

                if (strConvertSQL.Trim().ToLower().StartsWith("insert") ||
                   strConvertSQL.Trim().ToLower().StartsWith("update") ||
                   strConvertSQL.Trim().ToLower().StartsWith("delete"))
                {
                    lstInsertField = new List<string>();
                    strConvertSQL = ProcessNullValue(strConvertSQL, dicValue, lstInsertField);

                    if (strConvertSQL.Trim().ToLower().StartsWith("insert"))
                    {
                        strFields = new string[lstInsertField.Count];
                        oArgs = new object[lstInsertField.Count];

                        for (int i = 0; i < lstInsertField.Count; i++)
                        {
                            strFields[i] = lstInsertField[i];
                            oArgs[i] = dicValue.ContainsKey(lstInsertField[i]) ? dicValue[lstInsertField[i]] : null;
                        }
                    }

                    iUpdateRowCount = Database.ExecuteUpdate(strConvertSQL,
                                                             strFields, oArgs);

                    if (iUpdateRowCount < 0)
                        ErrorMessage = Database.ErrorMessage;

                    return iUpdateRowCount >= 0;
                }

                DbDataReader reader = Database.ExecuteQuery(strConvertSQL,
                                                            strFields, oArgs);
                DateTime dtTime2 = DateTime.Now;
                if (reader == null)
                {
                    ErrorMessage = Database.ErrorMessage;
                    return false;
                }
                AddData(oDataName, reader);
                DateTime dtTime3 = DateTime.Now;
                isSuccess = true;
            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message + "\n" + ex.StackTrace;
                isSuccess = false;
            }
            return isSuccess;
        }

        public override bool ExecuteCommand(object oDataSet)
        {
            try
            {
                foreach (object oDataName in SQLNameList)
                {
                    if (NeedExecute(oDataSet, oDataName) == false)
                        continue;

                    if (ExecuteSQL(oDataName, GetSQLStatement(oDataName)) == false)
                        return false;

                }
            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message + "\n" + ex.StackTrace;
                return false;
            }
            return true;
        }
    }
}
